package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BysjException;
import cn.edu.sdjzu.xg.eagle.util.CommonDao;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;
import cn.edu.sdjzu.xg.eagle.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;

public class SchoolDao {
    private static SchoolDao instance = new SchoolDao();

    private SchoolDao() {}

    public static SchoolDao getInstance() {
        return instance;
    }
    public static boolean add(Connection connection, School school) throws SQLException, BysjException {
        String sql_insert = "INSERT INTO school (description, no, remarks) VALUES (?, ?, ?)";
        PreparedStatement pstmt_insert =
                connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1, school.getDescription());
        pstmt_insert.setString(2, school.getNo());
        pstmt_insert.setString(3, school.getRemarks());
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
    public Collection<School> findAll(Pagination pagination) throws SQLException, BysjException {
        Collection<School> desiredSchools = new HashSet<>();
        Connection connection = JdbcHelper.getConn();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM School ");
        int totalNum = CommonDao.getInstance().count(connection, "School");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select_all.executeQuery();
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            School desiredSchool = new School(Math.toIntExact(id), description, no, remarks);
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet, pstmt_select_all, connection);
        return desiredSchools;
    }

    public void add(School schoolToAdd) throws BysjException, SQLException {
        try {
            Connection connection = JdbcHelper.getConn();
            boolean added = add(connection, schoolToAdd);
            if (added) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }
            JdbcHelper.close(connection);
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new BysjException();
        }
    }
}
